﻿using Model;
using System.Data;

namespace BLL
{
    public class Sys_RoleBLL : BaseBLL
    {
        public DataTable GetAllInfo()
        {
            string sql = string.Format(@"SELECT
    sysr.ROLE_ID,
    sysr.ROLE_NAME,
    CASE sysr.ROLE_STATE
    WHEN '0' THEN '禁用'
    WHEN '1' THEN '使用'
    END as ROLE_STATE
FROM
    Sys_Role sysr
ORDER BY sysr.ROLE_ID");
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetAllInfo2()
        {
            string sql = string.Format(@"SELECT
	sysr.ROLE_ID,
	sysr.ROLE_NAME,
	CASE sysr.ROLE_STATE
	WHEN '0' THEN '禁用'
	WHEN '1' THEN '使用'
	END as ROLE_STATE
FROM
	Sys_Role sysr WHERE sysr.ROLE_STATE='1'
ORDER BY sysr.ROLE_ID");
            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable GetInfoByCondition(string Type, object param)
        {
            string sql = string.Format(@"SELECT
    sysr.ROLE_ID,
    sysr.ROLE_NAME,
    CASE sysr.ROLE_STATE
    WHEN '0' THEN '禁用'
    WHEN '1' THEN '使用'
    END as ROLE_STATE
FROM
    Sys_Role sysr
WHERE 1=1");
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');
                if (!string.IsNullOrEmpty(str[0]))
                {
                    sql += " AND sysr.ROLE_NAME like '%" + str[0] + "%'";
                }
                if (!string.IsNullOrEmpty(str[1]))
                {
                    sql += " AND sysr.ROLE_STATE = " + str[1];
                }
            }
            if (Type == "RoleIds")
            {
                sql += " AND sysr.ROLE_ID IN(" + param.ToString() + ")";
            }
            sql += " ORDER BY sysr.ROLE_ID";
            //   DataTable dt = DbHelperSQL.GetDataTable(sql);

            DataTable dt = Query(sql);
            return dt;
        }

        public DataTable findLikeName(string name)
        {
            string sql = "SELECT * FROM THKQS.Sys_Role WHERE ROLE_NAME like '%" + name + "%'  ORDER BY ROLE_ID";
            return Query(sql);
        }

        public Sys_Role GetInfoById(string id)
        {
            string sql = string.Format(@"SELECT
    sysr.ROLE_ID,
    sysr.ROLE_NAME,
    sysr.ROLE_STATE
FROM
    THKQS.Sys_Role sysr
WHERE sysr.ROLE_ID=" + id);

            DataTable dt = Query(sql);
            Sys_Role model = new Sys_Role();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.ROLE_ID = decimal.Parse(dt.Rows[0]["ROLE_ID"].ToString());
                model.ROLE_NAME = dt.Rows[0]["ROLE_NAME"].ToString();
                model.ROLE_STATE = dt.Rows[0]["ROLE_STATE"].ToString();
            }
            return model;
        }

        public int INSERT(Sys_Role model, string operName)
        {
            //获取序列
            model.ROLE_ID = GetSequence("ROLE_ID");

            string sql = string.Format(@"INSERT INTO Sys_Role(role_id,ROLE_NAME,ROLE_STATE) VALUES('{0}','{1}','{2}')", model.ROLE_ID, model.ROLE_NAME, model.ROLE_STATE);
            int count = NoQuery(sql, operName);
            return count;
        }

        public int UPDATE(Sys_Role model, string operName)
        {
            string sql = string.Format(@"UPDATE Sys_Role SET
Sys_Role.ROLE_NAME='{0}',
Sys_Role.ROLE_STATE='{1}'
WHERE Sys_Role.ROLE_ID={2}
", model.ROLE_NAME, model.ROLE_STATE, model.ROLE_ID);
            int count = NoQuery(sql, operName);
            return count;
        }

        public int DELETE(string ids, string operName)
        {
            if (string.IsNullOrEmpty(ids))
            {
                return 0;
            }
            string sql = string.Format(@"DELETE FROM THKQS.Sys_Role WHERE Sys_Role.ROLE_ID IN(" + ids + ")");
            int count = NoQuery(sql, operName);
            return count;
        }

        public int RoleIsExit(string RoleName)
        {
            string sql = @"SELECT count(1) rs FROM THKQS.Sys_Role WHERE ROLE_NAME='" + RoleName + "'";
            return GetCount(sql);
        }

        public string GetRoleNames(string ids)
        {
            string sql = @"SELECT t.ROLE_NAME FROM Sys_Role t WHERE t.ROLE_ID IN (" + ids + ")";
            DataTable dt = Query(sql);

            string names = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    names += item[0].ToString() + ",";
                }
            }

            if (names.Length > 0)
            {
                names = names.Substring(0, names.Length - 1);
            }

            return names;
        }

        public string GetRoleName(string name)
        {
            string sql = @"SELECT t.ROLE_NAME FROM Sys_Role t WHERE t.ROLE_NAME IN ('" + name + "')";
            DataTable dt = Query(sql);

            string names = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow item in dt.Rows)
                {
                    names += item[0].ToString() + ",";
                }
            }

            if (names.Length > 0)
            {
                names = names.Substring(0, names.Length - 1);
            }

            return names;
        }

        public int GetRecordCount(string Type, object param)
        {
            string sql = string.Format(@"SELECT count(1) FROM Sys_Role sysr WHERE 1=1 ");
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');
                if (!string.IsNullOrEmpty(str[0]))
                {
                    sql += " AND sysr.ROLE_NAME like '%" + str[0] + "%'";
                }
                if (!string.IsNullOrEmpty(str[1]))
                {
                    sql += " AND sysr.ROLE_STATE = " + str[1];
                }
            }
            if (Type == "RoleIds")
            {
                sql += " AND sysr.ROLE_ID IN(" + param.ToString() + ")";
            }
            return GetCount(sql);
        }

        public DataTable GetListByPage(string Type, object param, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY sysr." + orderby + @") as r,
    sysr.ROLE_ID,
    sysr.ROLE_NAME,
    CASE sysr.ROLE_STATE
    WHEN '0' THEN '禁用'
    WHEN '1' THEN '使用'
    END as ROLE_STATE
FROM
    Sys_Role sysr
WHERE 1=1 ");
            if (Type == "Condition")
            {
                string strs = param.ToString();
                string[] str = strs.Split(',');
                if (!string.IsNullOrEmpty(str[0]) && !string.IsNullOrEmpty(str[0]) && str[0] != null)
                {
                    sql += " AND sysr.ROLE_NAME like '%" + str[0] + "%'";
                }
                if (!string.IsNullOrEmpty(str[1]))
                {
                    sql += " AND sysr.ROLE_STATE = " + str[1];
                }
            }
            if (Type == "RoleIds")
            {
                sql += " AND sysr.ROLE_ID IN(" + param.ToString() + ")";
            }
            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }
            return Query(sql);
        }
    }
}